﻿
Imports System.Data.OleDb

Public Class Location
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        displayTable(Request.Params("sort"))

    End Sub
    Private Sub displayTable(ByVal sortby As String)
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("Outletsdb").ConnectionString)


        oleDbConn.Open()
        Dim cmdQuery As String

        Select Case sortby
            Case "Area"
                cmdQuery = "SELECT * FROM location ORDER BY " & sortby
            Case "Cuisine"
                cmdQuery = "SELECT * FROM location ORDER BY " & sortby
            Case "Meal Type"
                cmdQuery = "SELECT * FROM location ORDER BY " & sortby
            Case "Email"
                cmdQuery = "SELECT * FROM location ORDER BY " & sortby
            Case "Posted By"
                cmdQuery = "SELECT * FROM location ORDER BY " & sortby
            Case Else
                cmdQuery = "SELECT * FROM location"

        End Select

        Dim dbcomm = New OleDbCommand(cmdQuery, oleDbConn)
        Dim dbread = dbcomm.ExecuteReader()

        Dim t As New Table

        Dim thr As New TableHeaderRow


        thr.Controls.Add(getHeaderCellWithText("Area"))
        thr.Controls.Add(getHeaderCellWithText("Cuisine"))
        thr.Controls.Add(getHeaderCellWithText("Meal Type"))
        thr.Controls.Add(getHeaderCellWithText("Email"))
        thr.Controls.Add(getHeaderCellWithText("Posted By"))

        t.Controls.Add(thr)

        While dbread.Read()
            Dim tr As New TableRow()

            tr.Controls.Add(getCellWithText(dbread.Item("Area")))
            tr.Controls.Add(getCellWithText(dbread.Item("Cuisine")))
            tr.Controls.Add(getCellWithText(dbread.Item("Meal Type"))) ', dbread.Item("Hyperlink")))
            tr.Controls.Add(getCellWithText(dbread.Item("Email")))
            tr.Controls.Add(getCellWithText(dbread.Item("Posted By")))

            t.Controls.Add(tr)

            Dim tr2 As New TableRow

            tr2.Controls.Add(getWideCellWithText(dbread.Item("Comments")))

            t.Controls.Add(tr2)

        End While

        PlaceHolder1.Controls.Add(t)


    End Sub


    Private Function getCellWithText(ByVal cellStr As String)
        Dim mycell As New TableCell
        'mycell.Text = cellStr
        mycell.Controls.Add(New LiteralControl("<B>" & cellStr & "</B>"))

        Return mycell
    End Function
    Private Function getWideCellWithText(ByVal cellStr As String)
        Dim mycell As New TableCell
        mycell.ColumnSpan = 3
        'mycell.Text = cellStr
        mycell.Controls.Add(New LiteralControl("<I>" & cellStr & "</I>"))

        Return mycell
    End Function

    Private Function getCellWithText(ByVal cellStr As String, ByVal UrlAddress As String)
        Dim mycell As New TableCell
        'mycell.Text = cellStr
        'mycell.Controls.Add(New LiteralControl("<B>" & cellStr & "</B>"))
        Dim h1 As New HyperLink
        h1.NavigateUrl = UrlAddress
        h1.Text = cellStr
        mycell.Controls.Add(h1)
        Return mycell
    End Function

    Private Function getHeaderCellWithText(ByVal cellStr As String)
        Dim mycell As New TableHeaderCell
        Dim hl As New HyperLink
        hl.Text = cellStr
        hl.NavigateUrl = "location.aspx?sort=" & cellStr
        mycell.Controls.Add(hl)
        Return mycell

    End Function



End Class